<html>
<head>
<script>
	function course(q)
	{
		var student = document.getElementById('student_id').value;
		window.location.href = 'enroll_past.jsp?selected_student=' + student + '&selected_course=' + q;
	}
</script>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.io.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Some parameters for section.
						String selectedStudent = request.getParameter("selected_student");
						String selectedCourse = request.getParameter("selected_course");
						
						// Get current quarter.
						InputStream in = application.getResourceAsStream("settings.properties");
						Properties p = new Properties();
						p.load(in);
						String quarter = p.getProperty("quarter");
						String year = p.getProperty("year");
						
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							PreparedStatement pstmt = conn
									.prepareStatement("INSERT INTO enroll VALUES (?, ?, ?, ?, ?, ?)");
							int section_id = Integer.parseInt(request.getParameter("section_id"));
							pstmt.setInt(1, section_id);
							pstmt.setString(2, request.getParameter("student_id"));
							pstmt.setString(3, request.getParameter("grade_type"));
							pstmt.setInt(4, Integer.parseInt(request.getParameter("units")));
							Statement statement = conn.createStatement();
							ResultSet rsCount = statement.executeQuery("SELECT count(*) FROM enroll WHERE section_id = " + section_id);
							rsCount.next();
							int enrollmentCount = rsCount.getInt(1);
							System.out.println(enrollmentCount);
							ResultSet rsLimit = statement.executeQuery("SELECT limit FROM section WHERE section_id = " + section_id);
							rsLimit.next();
							int limit = rsLimit.getInt(1);
							pstmt.setString(5, "");
							pstmt.setString(6, request.getParameter("grade"));
							pstmt.executeUpdate();
							
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                     	
							PreparedStatement pstmt = conn.prepareStatement("UPDATE enroll SET units = ?, " + 
									"grade_type = ?, grade = ? WHERE section_id = ? AND student_id = ?");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("units")));
							pstmt.setString(2, request.getParameter("grade_type"));
							pstmt.setString(3, request.getParameter("grade"));
							pstmt.setInt(4, Integer.parseInt(request.getParameter("section_id")));
							pstmt.setString(5, request.getParameter("student_id"));
							pstmt.executeUpdate();
	                     	
							// Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						// Check if a delete is requested
	                    if (action != null && action.equals("delete")) {
	                    	// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // DELETE the student FROM the committee table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "DELETE FROM enroll WHERE section_id = ? AND student_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));
	                        pstmt.setString(2, request.getParameter("student_id"));
	                        pstmt.executeUpdate();

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
	                    }
						
						// Create the statement
						Statement statement = conn.createStatement();
						
						// Get courses that has classes in past quarters.
						String query = "SELECT DISTINCT c.course_number FROM course c, class cl WHERE cl.course_number = c.course_number AND " +
								"(cl.quarter != ? OR cl.year != ?)";
						PreparedStatement pstmt = conn.prepareStatement(query);
						pstmt.setString(1, quarter);
						pstmt.setString(2, year);
						ResultSet rsCo = pstmt.executeQuery();
						CachedRowSet rsCourse = new CachedRowSetImpl();
						rsCourse.populate(rsCo);
						List<String> currCourses = new ArrayList<String>();
						while (rsCourse.next()) {
							currCourses.add(rsCourse.getString("course_number"));
						}
						
						// Use the created statement to SELECT
						// the student attributes.
						SortedMap<String, String> students = new TreeMap<String, String>();
						ResultSet rsStudent = statement.executeQuery("SELECT student_id, first_name, last_name FROM student");
						while (rsStudent.next()) {
							String info = rsStudent.getString("first_name") + " " + rsStudent.getString("last_name");
							students.put(rsStudent.getString("student_id"), info);
						}
						
						// SELECT enrollment
						pstmt = conn.prepareStatement("SELECT * FROM course c, enroll e, section s, class cl " + 
								"WHERE cl.class_id = s.class_id AND e.section_id = s.section_id AND " + 
								"cl.course_number = c.course_number AND (cl.quarter != ? OR cl.year != ?) ORDER BY e.student_id");
						pstmt.setString(1, quarter);
						pstmt.setString(2, year);
						ResultSet rsList = pstmt.executeQuery();
						CachedRowSet rs = new CachedRowSetImpl();
						rs.populate(rsList);
				%>
				<table border="1">
					<tr>
						<th>Student</th>
						<th>Course</th>
						<th>Section</th>
						<th>Units</th>
						<th>Grade Type</th>
						<th>Grade</th>
					</tr>
					<tr>
						<form action="enroll_past.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<th>
								<select id="student_id" name="student_id" style="width:200px">
								<%
									for (Map.Entry<String, String> student : students.entrySet()) {
								%>
						 			<option value="<%= student.getKey() %>" <% if (student.getKey().equals(selectedStudent)) out.print("selected='true'"); %>>
						 				<%= student.getValue() + " (" + student.getKey() + ")" %>
						 			</option>
								<%
									}
								%>
								</select>
							</th>
							<th>
								<select name="course_number" style="width:100px" onchange="course(this[selectedIndex].value)">
									<option></option>
								<%
									for (String course : currCourses) {
								%>
									<option value="<%= course %>" <% if (course.equals(selectedCourse)) out.print("selected='true'"); %>>
										<%= course %>
									</option>
								<%
									}
								%>
								</select>
							</th>
							<th>
								<%
									if (selectedCourse != null && !selectedCourse.isEmpty()) {
										%><select name="section_id" style="width:200px"><%
										PreparedStatement pstmtSection = conn.prepareStatement("SELECT * FROM class cl, section s WHERE " +
												"s.class_id = cl.class_id AND cl.course_number = ? AND " + 
												"(cl.quarter != ? OR cl.year != ?)");
										pstmtSection.setString(1, selectedCourse);
										pstmtSection.setString(2, quarter);
										pstmtSection.setString(3, year);
										ResultSet rsSection = pstmtSection.executeQuery();
										while (rsSection.next()) {
											String section = rsSection.getString("faculty_name") + " (" + rsSection.getInt("section_id") + ", " +
													rsSection.getString("quarter") + " "  + rsSection.getInt("year") + ")";
										%>
											<option value="<%= rsSection.getInt("section_id") %>">
												<%= section %>
											</option>
										<%
										}
										%></select><%
									}
								%>
							</th>
							<th>
								<%
									PreparedStatement pstmtCourse = conn.prepareStatement("SELECT min_units, max_units FROM course WHERE course_number = ?");
									pstmtCourse.setString(1, selectedCourse);
									ResultSet rsC = pstmtCourse.executeQuery();
									if (rsC.next()) {
										int minUnits = rsC.getInt("min_units");
										int maxUnits = rsC.getInt("max_units");
									%>
										<select name="units" style="width:100px">
											<%
												for (int i = minUnits; i <= maxUnits; i++) {
													%> <option value="<%= i %>"><%= i %></option> <%
												}
											%>
										</select>
									<%
									}
								%>
							</th>
							<th>
								<%
									if (selectedCourse != null && !selectedCourse.isEmpty()) {
										PreparedStatement pstmtC = conn.prepareStatement("SELECT * FROM course WHERE course_number = ?");
										pstmtC.setString(1, selectedCourse);
										rsC = pstmtC.executeQuery();
										rsC.next();
								%>
								<select name="grade_type" style="width:100px">
									<% if (rsC.getString("letter_grade_allowed").equals("YES")) { %>
										<option value="letter">letter</option>
									<% } %>
									<% if (rsC.getString("su_allowed").equals("YES")) { %>
										<option value="S/U">S/U</option>
									<% } %>
								</select>
								<%
									}
								%>
							</th>
							<th>
								<input name="grade" size="10">
							</th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
						// Iterate over the ResultSet
						while (rs.next()) {
					%>
					<tr>
                        <form action="enroll_past.jsp" method="POST">
                        	<input type="hidden" value="update" name="action">
                        	<td>
                        		<input type="hidden" value="<%= rs.getString("student_id") %>" name="student_id">
                        		<%= students.get(rs.getString("student_id")) + " (" + rs.getString("student_id") + ")" %>
                        	</td>
                        	<td><%= rs.getString("course_number") %></td>
                        	<td>
                        		<input type="hidden" value="<%= rs.getInt("section_id") %>" name="section_id">
                        		<%= rs.getString("faculty_name") + " (" + rs.getInt("section_id") + ", " +
										rs.getString("quarter") + " "  + rs.getInt("year") + ")" %>
                        	</td>
                        	<td>
                        		<%
									pstmtCourse = conn.prepareStatement("SELECT min_units, max_units FROM course WHERE course_number = ?");
									pstmtCourse.setString(1, rs.getString("course_number"));
									rsC = pstmtCourse.executeQuery();
									if (rsC.next()) {
										int minUnits = rsC.getInt("min_units");
										int maxUnits = rsC.getInt("max_units");
									%>
										<select name="units" style="width:100px">
											<%
												for (int i = minUnits; i <= maxUnits; i++) {
													%> <option value="<%= i %>" <% if (rs.getInt("units") == i) out.print("selected='selected'"); %>><%= i %></option> <%
												}
											%>
										</select>
									<%
									}
								%>
                        	</td>
                        	<td>
                        		<% String gradeType = rs.getString("grade_type"); %>
								<select name="grade_type" style="width:100px">
									<% if (rs.getString("letter_grade_allowed").equals("YES")) { %>
										<option value="letter" <% if (gradeType.equals("letter")) out.print("selected='selected'"); %>>letter</option>
									<% } %>
									<% if (rs.getString("su_allowed").equals("YES")) { %>
										<option value="S/U" <% if (gradeType.equals("S/U")) out.print("selected='selected'"); %>>S/U</option>
									<% } %>
								</select>
							</td>
							<td><input name="grade" value="<%= rs.getString("grade") %>" size="10"></td>
                        	<td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        <form action="enroll_past.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%= rs.getString("section_id") %>" name="section_id">
                            <input type="hidden" 
                                value="<%= rs.getString("student_id") %>" name="student_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
                <%
						}
                %>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>